package com.dao.impl;

import com.dao.BaseDao;
import com.dao.Medicine_infoDao;
import com.pojo.Medicine_info;
import com.pojo.Supplier_info;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * 描述：
 * <p>
 * 作者：jiangjiahao
 * 时间：2023年06月29日 10:33
 */
public class Medicine_infoDaoImpl implements Medicine_infoDao {

    private Connection conn = null;
    private PreparedStatement ps = null;
    private ResultSet rs = null;

    /**
     * 分页查询
     * @param index
     * @param size
     * @return
     */
    @Override
    public List<Medicine_info> pagequery(int index, int size, String query) {
        BaseDao baseDao = new BaseDao();
        conn = baseDao.getConn();
        String sql = "select * from medicine_info where 1=1";
        if (query != null && query != ""){
            sql += " and (NAME like '%"+query+"%' or CATEGORY like '%"+query+"%')";
        }
        sql += " and STATUS=0 order by MEDICINE_ID desc limit ?,?";
        List<Medicine_info> medicine_infolist = new ArrayList<>();
        try {
            ps = conn.prepareStatement(sql);
            ps.setInt(1, (index-1)*size);
            ps.setInt(2, size);
            rs = ps.executeQuery();
            while (rs.next()){
                int id = rs.getInt("MEDICINE_ID");
                String name = rs.getString("NAME");
                String category = rs.getString("CATEGORY");
                int supplierid = rs.getInt("SUPPLIERID");
                Supplier_infoDaoImpl supplier_infoDao = new Supplier_infoDaoImpl();
                Supplier_info supplier = supplier_infoDao.findSupplierById(supplierid);
                BigDecimal price = rs.getBigDecimal("UNIT_PRICE");
                Date outtime = rs.getDate("EXPIRATION_DATE");
                int status = rs.getInt("STATUS");
                Medicine_info medicine_info = new Medicine_info(id, name, category, supplier, price, null, outtime, status);
                medicine_infolist.add(medicine_info);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            baseDao.closeAll(conn, ps, rs);
        }
        return medicine_infolist;
    }

    /**
     * 查询药品信息总数
     * @return
     */
    @Override
    public Integer querycount(String query) {
        BaseDao baseDao = new BaseDao();
        conn = baseDao.getConn();
        String sql = "select count(1) from medicine_info where 1=1";
        if (query != null && query != ""){
            sql += " and (NAME like '%"+query+"%' or CATEGORY like '%"+query+"%')";
        }
        sql += " and STATUS=0";
        int count = 0;
        try {
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            if (rs.next()){
                count = rs.getInt(1);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            baseDao.closeAll(conn, ps, rs);
        }
        return count;
    }

    /**
     * 根据药品id查药品信息
     * @param id
     * @return
     */
    @Override
    public Medicine_info queryMedicineInfoById(int id) {
        String sql = "select * from medicine_info where MEDICINE_ID = ?";
        conn = BaseDao.getConn();
        Medicine_info medicineInfo = null;
        try {
            ps = conn.prepareStatement(sql);
            ps.setObject(1, id);
            rs = ps.executeQuery();
            if (rs.next()){
                int medicineId = rs.getInt("MEDICINE_ID");
                String name = rs.getString("NAME");
                String category = rs.getString("CATEGORY");
                String mf = rs.getString("SUPPLIERID");
                int supplierid = rs.getInt("SUPPLIERID");
                Supplier_infoDaoImpl supplier_infoDao = new Supplier_infoDaoImpl();
                Supplier_info supplier = supplier_infoDao.findSupplierById(supplierid);
                BigDecimal price = rs.getBigDecimal("UNIT_PRICE");
                Date outtime = rs.getDate("EXPIRATION_DATE");
                int status = rs.getInt("STATUS");
                medicineInfo = new Medicine_info(medicineId, name, category, supplier, price, null, outtime, status);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            BaseDao.closeAll(conn, ps, rs);
        }
        return medicineInfo;
    }

    /**
     * 根据药品名称查药品信息
     * @param name
     * @return
     */
    @Override
    public Medicine_info queryMedicineInfoByName(String name) {
        String sql = "select * from medicine_info where NAME = ?";
        conn = BaseDao.getConn();
        Medicine_info medicineInfo = null;
        try {
            ps = conn.prepareStatement(sql);
            ps.setObject(1, name);
            rs = ps.executeQuery();
            if (rs.next()){
                int medicineId = rs.getInt("MEDICINE_ID");
                String category = rs.getString("CATEGORY");
                String mf = rs.getString("SUPPLIERID");
                int supplierid = rs.getInt("SUPPLIERID");
                Supplier_infoDaoImpl supplier_infoDao = new Supplier_infoDaoImpl();
                Supplier_info supplier = supplier_infoDao.findSupplierById(supplierid);
                BigDecimal price = rs.getBigDecimal("UNIT_PRICE");
                Date outtime = rs.getDate("EXPIRATION_DATE");
                int status = rs.getInt("STATUS");
                medicineInfo = new Medicine_info(medicineId, name, category, supplier, price, null, outtime, status);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            BaseDao.closeAll(conn, ps, rs);
        }
        return medicineInfo;
    }

    /**
     * 根据药品id做删除（假删除）
     * @param id
     */
    @Override
    public void deleteMedicineInfoById(int id) {
        conn = BaseDao.getConn();
        String sql = "update medicine_info set STATUS=1 where MEDICINE_ID=?";
        BaseDao.ExcuteUpdate(sql, id);
    }

    /**
     * 添加药品信息
     * @param medicine_info
     */
    @Override
    public void addMedicineInfo(Medicine_info medicine_info) {
        conn = BaseDao.getConn();
        String sql = "insert into medicine_info values(null,?,?,?,?,0,?,0)";
        BaseDao.ExcuteUpdate(sql,
                medicine_info.getNAME(),
                medicine_info.getCATEGORY(),
                medicine_info.getSupplier_info().getSupplierId(),
                medicine_info.getUNIT_PRICE(),
                medicine_info.getEXPIRATION_DATE()
                );
    }

}




























